package com.wust.modules.codegenerate.database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.List;

import com.wust.modules.codegenerate.util.CodeUtil;
import com.wust.modules.codegenerate.generate.pojo.ColumnVo;
import com.wust.modules.codegenerate.generate.util.StringDealUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import com.wust.modules.codegenerate.database.util.FieldUtil;

/**
 * @author wanheng
 */
@Slf4j
public class DbReadTableUtil {

    private static Connection connection;
    private static Statement statement;

    public static List<String> getTableNames() throws SQLException {
        String sql = null;
        ArrayList arrayList = new ArrayList(0);

        try {
            Class.forName(CodeUtil.url);
            connection = DriverManager.getConnection(CodeUtil.username, CodeUtil.password, CodeUtil.databaseName);
            statement = connection.createStatement(1005, 1007);
            if ("mysql".equals(CodeUtil.diverName)) {
                sql = MessageFormat.format("select distinct table_name from information_schema.columns where table_schema = {0}", StringDealUtil.addQuotes(CodeUtil.databaseName1));
            }

            if ("oracle".equals(CodeUtil.diverName)) {
                sql = " select distinct colstable.table_name as  table_name from user_tab_cols colstable order by colstable.table_name";
            }

            if ("postgresql".equals(CodeUtil.diverName)) {
                sql = "select tablename from pg_tables where schemaname='public'";
            }

            if ("sqlserver".equals(CodeUtil.diverName)) {
                sql = "select distinct c.name as  table_name from sys.objects c where c.type = 'U' ";
            }

            log.debug("--------------sql-------------" + sql);
            ResultSet resultSet = statement.executeQuery(sql);

            while(resultSet.next()) {
                String result = resultSet.getString(1);
                arrayList.add(result);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                    statement = null;
                    System.gc();
                }

                if (connection != null) {
                    connection.close();
                    connection = null;
                    System.gc();
                }
            } catch (SQLException e) {
                throw e;
            }

        }

        return arrayList;
    }

    public static List<ColumnVo> getColumns(String tableName) throws Exception {
        String sql = null;
        ArrayList arrayList = new ArrayList();

        ColumnVo columnVo;
        try {
            Class.forName(CodeUtil.url);
            connection = DriverManager.getConnection(CodeUtil.username, CodeUtil.password, CodeUtil.databaseName);
            statement = connection.createStatement(1005, 1007);
            if ("mysql".equals(CodeUtil.diverName)) {
                sql = MessageFormat.format("select column_name,data_type,column_comment,numeric_precision,numeric_scale,character_maximum_length,is_nullable nullable from information_schema.columns where table_name = {0} and table_schema = {1} order by ORDINAL_POSITION", StringDealUtil.addQuotes(tableName), StringDealUtil.addQuotes(CodeUtil.databaseName1));
            }

            if ("oracle".equals(CodeUtil.diverName)) {
                sql = MessageFormat.format(" select colstable.column_name column_name, colstable.data_type data_type, commentstable.comments column_comment, colstable.Data_Precision column_precision, colstable.Data_Scale column_scale,colstable.Char_Length,colstable.nullable from user_tab_cols colstable  inner join user_col_comments commentstable  on colstable.column_name = commentstable.column_name  where colstable.table_name = commentstable.table_name  and colstable.table_name = {0}", StringDealUtil.addQuotes(tableName.toUpperCase()));
            }

            if ("postgresql".equals(CodeUtil.diverName)) {
                sql = MessageFormat.format("select icm.column_name as field,icm.udt_name as type,fieldtxt.descript as comment, icm.numeric_precision_radix as column_precision ,icm.numeric_scale as column_scale ,icm.character_maximum_length as Char_Length,icm.is_nullable as attnotnull from information_schema.columns icm, (SELECT A.attnum,( SELECT description FROM pg_catalog.pg_description WHERE objoid = A.attrelid AND objsubid = A.attnum ) AS descript,A.attname FROM\tpg_catalog.pg_attribute A WHERE A.attrelid = ( SELECT oid FROM pg_class WHERE relname = {0} ) AND A.attnum > 0 AND NOT A.attisdropped  ORDER BY\tA.attnum ) fieldtxt where icm.table_name={1} and fieldtxt.attname = icm.column_name", StringDealUtil.addQuotes(tableName), StringDealUtil.addQuotes(tableName));
            }

            if ("sqlserver".equals(CodeUtil.diverName)) {
                sql = MessageFormat.format("select distinct cast(a.name as varchar(50)) column_name,  cast(b.name as varchar(50)) data_type,  cast(e.value as NVARCHAR(200)) comment,  cast(ColumnProperty(a.object_id,a.Name,'''Precision''') as int) num_precision,  cast(ColumnProperty(a.object_id,a.Name,'''Scale''') as int) num_scale,  a.max_length,  (case when a.is_nullable=1 then '''y''' else '''n''' end) nullable,column_id   from sys.columns a left join sys.types b on a.user_type_id=b.user_type_id left join (select top 1 * from sys.objects where type = '''U''' and name ={0}  order by name) c on a.object_id=c.object_id left join sys.extended_properties e on e.major_id=c.object_id and e.minor_id=a.column_id and e.class=1 where c.name={0} order by a.column_id", StringDealUtil.addQuotes(tableName));
            }

            log.debug("--------------sql-------------" + sql);
            ResultSet resultSet = statement.executeQuery(sql);
            resultSet.last();
            int row = resultSet.getRow();
            if (row <= 0) {
                throw new Exception("该表不存在或者表中没有字段");
            }

            columnVo = new ColumnVo();
            if (CodeUtil.flag) {
                columnVo.setFieldName(getLowerCamelCase(resultSet.getString(1).toLowerCase()));
            } else {
                columnVo.setFieldName(resultSet.getString(1).toLowerCase());
            }

            columnVo.setFieldDbName(resultSet.getString(1).toUpperCase());
            columnVo.setFieldType(getLowerCamelCase(resultSet.getString(2).toLowerCase()));
            columnVo.setFieldDbType(getLowerCamelCase(resultSet.getString(2).toLowerCase()));
            columnVo.setPrecision(resultSet.getString(4));
            columnVo.setScale(resultSet.getString(5));
            columnVo.setCharMaxLength(resultSet.getString(6));
            columnVo.setNullable(StringDealUtil.getYN(resultSet.getString(7)));
            setClassType(columnVo);
            columnVo.setFiledComment(StringUtils.isBlank(resultSet.getString(3)) ? columnVo.getFieldName() : resultSet.getString(3));
            log.debug("columnt.getFieldName() -------------" + columnVo.getFieldName());
            String[] strings = new String[0];
            if (CodeUtil.pageFilterFields != null) {
                strings = CodeUtil.pageFilterFields.toLowerCase().split(",");
            }

            if (!CodeUtil.dbTableId.equals(columnVo.getFieldName()) && FieldUtil.isInFiledList(columnVo.getFieldDbName().toLowerCase(), strings)) {
                arrayList.add(columnVo);
            }

            while(resultSet.previous()) {
                ColumnVo columnVo1 = new ColumnVo();
                if (CodeUtil.flag) {
                    columnVo1.setFieldName(getLowerCamelCase(resultSet.getString(1).toLowerCase()));
                } else {
                    columnVo1.setFieldName(resultSet.getString(1).toLowerCase());
                }

                columnVo1.setFieldDbName(resultSet.getString(1).toUpperCase());
                log.debug("columnt.getFieldName() -------------" + columnVo1.getFieldName());
                if (!CodeUtil.dbTableId.equals(columnVo1.getFieldName()) && FieldUtil.isInFiledList(columnVo1.getFieldDbName().toLowerCase(), strings)) {
                    columnVo1.setFieldType(getLowerCamelCase(resultSet.getString(2).toLowerCase()));
                    columnVo1.setFieldDbType(getLowerCamelCase(resultSet.getString(2).toLowerCase()));
                    log.debug("-----po.setFieldType------------" + columnVo1.getFieldType());
                    columnVo1.setPrecision(resultSet.getString(4));
                    columnVo1.setScale(resultSet.getString(5));
                    columnVo1.setCharMaxLength(resultSet.getString(6));
                    columnVo1.setNullable(StringDealUtil.getYN(resultSet.getString(7)));
                    setClassType(columnVo1);
                    columnVo1.setFiledComment(StringUtils.isBlank(resultSet.getString(3)) ? columnVo1.getFieldName() : resultSet.getString(3));
                    arrayList.add(columnVo1);
                }
            }

            log.debug("读取表成功");
        } catch (ClassNotFoundException e) {
            throw e;
        } catch (SQLException e) {
            throw e;
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                    statement = null;
                    System.gc();
                }

                if (connection != null) {
                    connection.close();
                    connection = null;
                    System.gc();
                }
            } catch (SQLException e) {
                throw e;
            }

        }

        ArrayList arrayList1 = new ArrayList();

        for(int i = arrayList.size() - 1; i >= 0; --i) {
            columnVo = (ColumnVo) arrayList.get(i);
            arrayList1.add(columnVo);
        }

        return arrayList1;
    }

    public static List<ColumnVo> getColumnList(String tbnam) throws Exception {
        ResultSet resultSet = null;
        String sql = null;
        ArrayList arrayList = new ArrayList();

        ColumnVo columnVo;
        try {
            Class.forName(CodeUtil.url);
            connection = DriverManager.getConnection(CodeUtil.username, CodeUtil.password, CodeUtil.databaseName);
            statement = connection.createStatement(1005, 1007);
            if ("mysql".equals(CodeUtil.diverName)) {
                sql = MessageFormat.format("select column_name,data_type,column_comment,numeric_precision,numeric_scale,character_maximum_length,is_nullable nullable from information_schema.columns where table_name = {0} and table_schema = {1} order by ORDINAL_POSITION", StringDealUtil.addQuotes(tbnam), StringDealUtil.addQuotes(CodeUtil.databaseName1));
            }

            if ("oracle".equals(CodeUtil.diverName)) {
                sql = MessageFormat.format(" select colstable.column_name column_name, colstable.data_type data_type, commentstable.comments column_comment, colstable.Data_Precision column_precision, colstable.Data_Scale column_scale,colstable.Char_Length,colstable.nullable from user_tab_cols colstable  inner join user_col_comments commentstable  on colstable.column_name = commentstable.column_name  where colstable.table_name = commentstable.table_name  and colstable.table_name = {0}", StringDealUtil.addQuotes(tbnam.toUpperCase()));
            }

            if ("postgresql".equals(CodeUtil.diverName)) {
                sql = MessageFormat.format("select icm.column_name as field,icm.udt_name as type,fieldtxt.descript as comment, icm.numeric_precision_radix as column_precision ,icm.numeric_scale as column_scale ,icm.character_maximum_length as Char_Length,icm.is_nullable as attnotnull from information_schema.columns icm, (SELECT A.attnum,( SELECT description FROM pg_catalog.pg_description WHERE objoid = A.attrelid AND objsubid = A.attnum ) AS descript,A.attname FROM\tpg_catalog.pg_attribute A WHERE A.attrelid = ( SELECT oid FROM pg_class WHERE relname = {0} ) AND A.attnum > 0 AND NOT A.attisdropped  ORDER BY\tA.attnum ) fieldtxt where icm.table_name={1} and fieldtxt.attname = icm.column_name", StringDealUtil.addQuotes(tbnam), StringDealUtil.addQuotes(tbnam));
            }

            if ("sqlserver".equals(CodeUtil.diverName)) {
                sql = MessageFormat.format("select distinct cast(a.name as varchar(50)) column_name,  cast(b.name as varchar(50)) data_type,  cast(e.value as NVARCHAR(200)) comment,  cast(ColumnProperty(a.object_id,a.Name,'''Precision''') as int) num_precision,  cast(ColumnProperty(a.object_id,a.Name,'''Scale''') as int) num_scale,  a.max_length,  (case when a.is_nullable=1 then '''y''' else '''n''' end) nullable,column_id   from sys.columns a left join sys.types b on a.user_type_id=b.user_type_id left join (select top 1 * from sys.objects where type = '''U''' and name ={0}  order by name) c on a.object_id=c.object_id left join sys.extended_properties e on e.major_id=c.object_id and e.minor_id=a.column_id and e.class=1 where c.name={0} order by a.column_id", StringDealUtil.addQuotes(tbnam));
            }

            resultSet = statement.executeQuery(sql);
            resultSet.last();
            int row = resultSet.getRow();
            if (row <= 0) {
                throw new Exception("该表不存在或者表中没有字段");
            }

            columnVo = new ColumnVo();
            if (CodeUtil.flag) {
                columnVo.setFieldName(getLowerCamelCase(resultSet.getString(1).toLowerCase()));
            } else {
                columnVo.setFieldName(resultSet.getString(1).toLowerCase());
            }

            columnVo.setFieldDbName(resultSet.getString(1).toUpperCase());
            columnVo.setPrecision(StringDealUtil.blankDeal(resultSet.getString(4)));
            columnVo.setScale(StringDealUtil.blankDeal(resultSet.getString(5)));
            columnVo.setCharMaxLength(StringDealUtil.blankDeal(resultSet.getString(6)));
            columnVo.setNullable(StringDealUtil.getYN(resultSet.getString(7)));
            columnVo.setFieldType(getClassType(resultSet.getString(2).toLowerCase(), columnVo.getPrecision(), columnVo.getScale()));
            columnVo.setFieldDbType(getLowerCamelCase(resultSet.getString(2).toLowerCase()));
            setClassType(columnVo);
            columnVo.setFiledComment(StringUtils.isBlank(resultSet.getString(3)) ? columnVo.getFieldName() : resultSet.getString(3));
            log.debug("columnt.getFieldName() -------------" + columnVo.getFieldName());
            arrayList.add(columnVo);

            while(true) {
                if (!resultSet.previous()) {
                    log.debug("读取表成功");
                    break;
                }

                ColumnVo columnVo1 = new ColumnVo();
                if (CodeUtil.flag) {
                    columnVo1.setFieldName(getLowerCamelCase(resultSet.getString(1).toLowerCase()));
                } else {
                    columnVo1.setFieldName(resultSet.getString(1).toLowerCase());
                }

                columnVo1.setFieldDbName(resultSet.getString(1).toUpperCase());
                columnVo1.setPrecision(StringDealUtil.blankDeal(resultSet.getString(4)));
                columnVo1.setScale(StringDealUtil.blankDeal(resultSet.getString(5)));
                columnVo1.setCharMaxLength(StringDealUtil.blankDeal(resultSet.getString(6)));
                columnVo1.setNullable(StringDealUtil.getYN(resultSet.getString(7)));
                columnVo1.setFieldType(getClassType(resultSet.getString(2).toLowerCase(), columnVo1.getPrecision(), columnVo1.getScale()));
                columnVo1.setFieldDbType(getLowerCamelCase(resultSet.getString(2).toLowerCase()));
                setClassType(columnVo1);
                columnVo1.setFiledComment(StringUtils.isBlank(resultSet.getString(3)) ? columnVo1.getFieldName() : resultSet.getString(3));
                arrayList.add(columnVo1);
            }
        } catch (ClassNotFoundException e) {
            throw e;
        } catch (SQLException e) {
            throw e;
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                    statement = null;
                    System.gc();
                }

                if (connection != null) {
                    connection.close();
                    connection = null;
                    System.gc();
                }
            } catch (SQLException e) {
                throw e;
            }

        }

        ArrayList arrayList1 = new ArrayList();

        for(int i = arrayList.size() - 1; i >= 0; --i) {
            columnVo = (ColumnVo) arrayList.get(i);
            arrayList1.add(columnVo);
        }

        return arrayList1;
    }

    private static String getLowerCamelCase(String s) {
        String[] strings = s.split("_");
        s = "";
        int i = 0;

        for(int j = strings.length; i < j; ++i) {
            if (i > 0) {
                String lowerCase = strings[i].toLowerCase();
                lowerCase = lowerCase.substring(0, 1).toUpperCase() + lowerCase.substring(1, lowerCase.length());
                s = s + lowerCase;
            } else {
                s = s + strings[i].toLowerCase();
            }
        }

        return s;
    }

    public static String getUpCamelCase(String s) {
        String[] strings = s.split("_");
        s = "";
        int i = 0;

        for(int j = strings.length; i < j; ++i) {
            if (i > 0) {
                String lowerCase = strings[i].toLowerCase();
                lowerCase = lowerCase.substring(0, 1).toUpperCase() + lowerCase.substring(1, lowerCase.length());
                s = s + lowerCase;
            } else {
                s = s + strings[i].toLowerCase();
            }
        }

        s = s.substring(0, 1).toUpperCase() + s.substring(1);
        return s;
    }

    private static void setClassType(ColumnVo columnVo) {
        String fieldType = columnVo.getFieldType();
        String scale = columnVo.getScale();
        columnVo.setClassType("inputxt");
        if ("N".equals(columnVo.getNullable())) {
            columnVo.setOptionType("*");
        }

        if (!"datetime".equals(fieldType) && !fieldType.contains("time")) {
            if ("date".equals(fieldType)) {
                columnVo.setClassType("easyui-datebox");
            } else if (fieldType.contains("int")) {
                columnVo.setOptionType("n");
            } else if ("number".equals(fieldType)) {
                if (StringUtils.isNotBlank(scale) && Integer.parseInt(scale) > 0) {
                    columnVo.setOptionType("d");
                }
            } else if (!"float".equals(fieldType) && !"double".equals(fieldType) && !"decimal".equals(fieldType)) {
                if ("numeric".equals(fieldType)) {
                    columnVo.setOptionType("d");
                }
            } else {
                columnVo.setOptionType("d");
            }
        } else {
            columnVo.setClassType("easyui-datetimebox");
        }

    }

    private static String getClassType(String type, String precision, String scale) {
        if (type.contains("char")) {
            type = "java.lang.String";
        } else if (type.contains("int")) {
            type = "java.lang.Integer";
        } else if (type.contains("float")) {
            type = "java.lang.Float";
        } else if (type.contains("double")) {
            type = "java.lang.Double";
        } else if (type.contains("number")) {
            if (StringUtils.isNotBlank(scale) && Integer.parseInt(scale) > 0) {
                type = "java.math.BigDecimal";
            } else if (StringUtils.isNotBlank(precision) && Integer.parseInt(precision) > 10) {
                type = "java.lang.Long";
            } else {
                type = "java.lang.Integer";
            }
        } else if (type.contains("decimal")) {
            type = "java.math.BigDecimal";
        } else if (type.contains("date")) {
            type = "java.util.Date";
        } else if (type.contains("time")) {
            type = "java.util.Date";
        } else if (type.contains("blob")) {
            type = "byte[]";
        } else if (type.contains("clob")) {
            type = "java.sql.Clob";
        } else if (type.contains("numeric")) {
            type = "java.math.BigDecimal";
        } else {
            type = "java.lang.Object";
        }

        return type;
    }
}
